package br.com.pontoe.model.persistencia;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import br.com.pontoe.model.conexao.Conexao;
import br.com.pontoe.model.entidades.Endereco;

/**
 * DAO para a entidade Endereco
 * 
 * @author Cleison
 * @version 1.0
 * @since 07/05/2010
 **/
public class EnderecoDAO implements DAOInterface<Endereco> {

	private StringBuilder sql = new StringBuilder();
	private Connection con = null;
	private PreparedStatement stm = null;
	
	
	@Override
	public int adicionar(Endereco entidade) throws SQLException {
		int ret = -1;
		
		try {
			sql = new StringBuilder();
			sql.append("INSERT INTO ENDERECO (IDENDERECO, LOGRADOURO, NUMERO, CEP, IDBAIRRO) ");
			sql.append("VALUES (NULL, ?, ?, ?, ?)");
			
			con = Conexao.getInstance().getConnection();
			stm = (PreparedStatement) con.prepareStatement(sql.toString());
			
			stm.setString(1, entidade.getLogradouro());
			stm.setInt(2, entidade.getNumero());
			stm.setInt(3, entidade.getCep());
			stm.setInt(4, entidade.getBairro().getId());
			stm.executeUpdate();
			ret =(int)stm.getLastInsertID();
		}
		finally {
			stm.close();
			con.close();
		}
		return ret;
	}

	@Override
	public boolean atualizar(Endereco entidade) throws SQLException {
		boolean ret = true;
		
		try {
			sql = new StringBuilder();
			sql.append("UPDATE ENDERECO ");
			sql.append("SET   LOGRADOURO = ?, NUMERO = ?, CEP = ?, IDBAIRRO = ? ");
			sql.append("WHERE IDENDERECO = ? ");
			
			con = Conexao.getInstance().getConnection();
			stm = (PreparedStatement) con.prepareStatement(sql.toString());
			
			stm.setString(1, entidade.getLogradouro());
			stm.setInt(2, entidade.getNumero());
			stm.setInt(3, entidade.getCep());
			stm.setInt(4, entidade.getBairro().getId());
			stm.setInt(5, entidade.getId());
			
			ret = stm.execute();
		}
		finally {
			stm.close();
			con.close();
		}
		return ret;
	}

	@Override
	public Endereco localizar(int id) throws SQLException {
		Endereco ret = null;
		
		try {
			sql = new StringBuilder();
			sql.append("SELECT  IDENDERECO, LOGRADOURO, NUMERO, CEP, IDBAIRRO ");
			sql.append("FROM 	ENDERECO ");
			sql.append("WHERE 	IDENDERECO = ? ");
			
			con = Conexao.getInstance().getConnection();
			stm = (PreparedStatement) con.prepareStatement(sql.toString());
			stm.setInt(1, id);
			
			ResultSet temp = stm.executeQuery();  
			BairroDAO bTmp = new BairroDAO();
			
			if(temp.next()) {
				ret = new Endereco();
				ret.setId(temp.getInt("IDENDERECO"));
				ret.setLogradouro(temp.getString("LOGRADOURO"));
				ret.setNumero(temp.getInt("NUMERO"));
				ret.setCep(temp.getInt("CEP"));
				ret.setBairro(bTmp.localizar(temp.getInt("IDBAIRRO")));
			}
			temp.close();
		}
		finally {
			stm.close();
			con.close();
		}
		return ret;
	}

	@Override
	public List<Endereco> localizarTodos() throws SQLException {
		List<Endereco> ret = new ArrayList<Endereco>();
		
		try {
			sql = new StringBuilder();
			sql.append("SELECT  IDENDERECO, LOGRADOURO, NUMERO, CEP, IDBAIRRO ");
			sql.append("FROM 	ENDERECO ");
			
			con = Conexao.getInstance().getConnection();
			stm = (PreparedStatement) con.prepareStatement(sql.toString());
			
			ResultSet temp = stm.executeQuery();  
			BairroDAO bTmp = new BairroDAO();
			
			while(temp.next()) {
				Endereco e = new Endereco();
				e.setId(temp.getInt("IDENDERECO"));
				e.setLogradouro(temp.getString("LOGRADOURO"));
				e.setNumero(temp.getInt("NUMERO"));
				e.setCep(temp.getInt("CEP"));
				e.setBairro(bTmp.localizar(temp.getInt("IDBAIRRO")));
			}
			temp.close();
		}
		finally {
			stm.close();
			con.close();
		}
		return ret;
	}

	@Override
	public boolean remover(Endereco entidade) throws SQLException {
		boolean ret = true;

		try {
			sql = new StringBuilder();
			sql.append("DELETE FROM ENDERECO ");
			sql.append("WHERE IDENDERECO = ? ");

			con = Conexao.getInstance().getConnection();
			stm = (PreparedStatement) con.prepareStatement(sql.toString());

			stm.setInt(1, entidade.getId());
			
			ret = stm.execute();
		} 
		finally {
			stm.close();
			con.close();
		}
		return ret;
	}

	/**
	 * Metodo Adicionados
	 * 
	 * @author Rodolfo
	 * @version 1.1
	 * @since 15/05/2010
	 **/
	public int adicionarRetId(Endereco entidade) throws SQLException {
		int id =-1;//add e retorna o id gerado pelo campo auto incremento
		
		try {
			sql = new StringBuilder();
			sql.append("INSERT INTO ENDERECO (IDENDERECO, LOGRADOURO, NUMERO, CEP, IDBAIRRO) ");
			sql.append("VALUES (NULL, ?, ?, ?, ?)");
			
			con = Conexao.getInstance().getConnection();
			stm = (PreparedStatement) con.prepareStatement(sql.toString());
			
			stm.setString(1, entidade.getLogradouro());
			stm.setInt(2, entidade.getNumero());
			stm.setInt(3, entidade.getCep());
			stm.setInt(4, entidade.getBairro().getId());
			stm.executeUpdate();
			id = LastInsertId();
			
		}
		finally {
			stm.close();
			con.close();
		}
		return id;
	}
	
	public Endereco locPorNumCep(int num,int cep) throws SQLException {
		Endereco ret = null;
		
		try {
			sql = new StringBuilder();
			sql.append("SELECT  IDENDERECO, LOGRADOURO, NUMERO, CEP, IDBAIRRO ");
			sql.append("FROM 	ENDERECO ");
			sql.append("WHERE 	NUMERO = ? AND CEP=?");
			
			con = Conexao.getInstance().getConnection();
			stm = (PreparedStatement) con.prepareStatement(sql.toString());
			stm.setInt(1, num);
			stm.setInt(2, cep);
			
			ResultSet temp = stm.executeQuery();  
			BairroDAO bTmp = new BairroDAO();
			
			if(temp.next()) {
				ret = new Endereco();
				ret.setId(temp.getInt("IDENDERECO"));
				ret.setLogradouro(temp.getString("LOGRADOURO"));
				ret.setNumero(temp.getInt("NUMERO"));
				ret.setCep(temp.getInt("CEP"));
				ret.setBairro(bTmp.localizar(temp.getInt("IDBAIRRO")));
			}
			temp.close();
		}
		finally {
			stm.close();
			con.close();
		}
		return ret;
	}

	public int LastInsertId()throws SQLException {
		int i=0;//retorna o ultimo numero gerado pela coluna autoIncremento da tabela
		
			sql = new StringBuilder();
			sql.append("select LAST_INSERT_ID() AS ID");
						
			con = Conexao.getInstance().getConnection();
			stm = (PreparedStatement) con.prepareStatement(sql.toString());
			
			ResultSet temp = stm.executeQuery();  
				
			while(temp.next()) { i =temp.getInt("ID");}
			temp.close();
				
		return i;
	}
}
